import json
import re
import os
import excel_info_sql
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
import requests
from urllib3.exceptions import InsecureRequestWarning

# 禁用安全请求警告
requests.urllib3.disable_warnings(InsecureRequestWarning)
list1 = excel_info_sql.getConditions()


def set_columns_width(sheet, columns_width):
    for index, width in enumerate(columns_width):
        sheet.column_dimensions[get_column_letter(index + 1)].width = width


def make_workbook(sheet_name=None):
    workbook = Workbook()
    sheet = workbook.active
    if sheet_name:
        sheet.title = sheet_name
    return workbook, sheet


def save_excel(workbook, name):
    filename = '{}.xlsx'.format(name)
    try:
        workbook.save(filename)
        workbook.close()
    except PermissionError:
        print('无法写入{}，请检查是否在Excel中打开了文件，如有需要请另存为并关闭'.format(filename))



def getlinkurl():
    try:
        os.remove("错误日志.log")
    except:
        pass
    print("开始获取数据")
    r = requests.session()
    for x in range(len(list1)):
        title = []
        first = second = third = four = five = six = Original_link = ""
        try:
            url = list1[x]["link_url"]
            if "http" in url:
                rr = r.get(url=url, verify=False)
                html = rr.text.replace('\r', '').replace('\t', '').replace('\n', '')
                if "此内容因违规无法查看" in html:
                    pass
                else:
                    link_url = re.findall('href="https://mp.weixin.qq(.*?)"', html)
                    Original_link = re.findall("msg_source_url = '(.*?)'", html)[0]
                    for link in link_url:
                        response = r.get(url="https://mp.weixin.qq" + link, verify=False).text \
                            .replace('\r', '').replace('\t', '').replace('\n', '')
                        if "该内容已被发布者删除" in response:
                            continue
                        else:
                            ht = re.findall('<strong class="profile_nickname">(.*?)</strong>', response)[0]
                        if ht not in title:
                            title.append(ht)
                try:
                    if len(title) == 2:
                        first = title[0]
                        second = title[1]
                    elif len(title) == 3:
                        first = title[0]
                        second = title[1]
                        third = title[2]
                    elif len(title) == 4:
                        first = title[0]
                        second = title[1]
                        third = title[2]
                        four = title[3]
                    elif len(title) == 5:
                        first = title[0]
                        second = title[1]
                        third = title[2]
                        four = title[3]
                        five = title[4]
                    elif len(title) == 6:
                        first = title[0]
                        second = title[1]
                        third = title[2]
                        four = title[3]
                        five = title[4]
                        six = title[5]
                    else:
                        first = title[0]
                except:
                    pass
        except Exception as e:
            with open("错误日志.log", "a", encoding="utf-8") as f:
                f.write(str(e) + "\r\n")
        infos = {
            "link": list1[x]["link_url"],
            "first": first,
            "second": second,
            "third": third,
            "four": four,
            "five": five,
            "six": six,
            "Original_link": Original_link
        }
        info = json.dumps(infos)
        with open("link_info.json", "a", encoding="utf-8") as f:
            f.write(info + "\r")


def date_excel():
    workbook, sheet = make_workbook()
    set_columns_width(sheet, [35, 20, 20, 20, 20, 20, 20, 55])
    sheet.append(['链接', '带号1', '带号2', '带号3', '带号4', '带号5', '带号6', '原文链接'])
    row_index = 1
    print("开始生成表格")
    with open("link_info.json", encoding='utf-8', mode='r', errors='ignore') as f:
        for lines in f.readlines():
            try:
                dic = json.loads(lines)
                row_index += 1
                list2 = [dic["link"], dic["first"], dic["second"], dic["third"], dic["four"], dic["five"], dic["six"]
                    , dic["Original_link"]]
                sheet.append(list2)
            except:
                print(lines)
                pass
    save_excel(workbook, '生成带号表格')


if __name__ == "__main__":
    getlinkurl()
    date_excel()
    os.remove("link_info.json")